package com.mach.platform.repository;

import com.mach.platform.domain.iot.IotDev;
import com.mach.platform.domain.iot.IotSpace;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author Anonymous
 * @Description: 设备
 * @Package: com.mach.platform.repository
 * @time 2018/04/27 23:12
 */
@Repository
public interface IotDevRepo extends BaseRepository<IotDev>{

	/**
	 * 临时插入碳排和成本2019之前的数据
	 * @param devId
	 * @param orderNum 排序
	 * @param theTime
	 * @param dataType
	 * @param value
	 * @return
	 */
	@Modifying
	@Query(value = "replace into iot_dev_data_win (dev_id, order_num, the_time, data_type, dvalue) values (?1, ?2, ?3, ?4, ?5)", nativeQuery = true)
	int insertWinData(Integer devId, Integer orderNum, String theTime, String dataType, double value);

	/**
	 * 临时查询电数据
	 * @param devIds
	 * @return
	 */
	@Query(value = "select * from iot_dev_data_win where dev_id in (?1) and the_time < '2019'", nativeQuery = true)
	List<Map> eleData(List<Integer> devIds);


	/**
	 * 通过多个空间id查询
	 * @return
	 */
	@Query(value = "select id from iot_dev where space_id in (?1)", nativeQuery = true)
	List<Long> findBySpaceIdIn(List<Long> spaceIds);

	/**
	 * 临时查询电数据
	 * @param devIds
	 * @return
	 */
	@Query(value = "select * from iot_dev_data_win where dev_id in (?1) and the_time < '2019'", nativeQuery = true)
	List<Map> gasData(List<Integer> devIds);

	/**
	 * 根据空间查找 设备
	 *
	 * @param iotSpace
	 * @return
	 */
	List<IotDev> getAllByIotSpace (IotSpace iotSpace);

	/**
	 * 根据空间查出所有的 设备数据
	 * @param iotSpaces
	 * @return
	 */
	List<IotDev> findAllByIotSpaceIn (List<IotSpace> iotSpaces);

	/**
	 * 根据空间id查询分页设备id和名称
	 * @param id 空间id
	 * @param pageable 分页参数
	 * @return
	 */
	@Query(value = "select id, name from iot_dev where space_id = ?1 order by sort",
			countQuery = "select count(*) from (select id, name from iot_dev where space_id = ?1) as dev", nativeQuery = true)
	Page<Map> pageIotDevBySpaceId(Long id, Pageable pageable);

	/**
	 * 查询母排测温下面的所有设备id
	 * @param spaceId 母排空间id
	 * @return
	 */
	@Query(value = "select id from iot_dev where space_id in (select id from iot_space where parent_id in" +
			" (select id from iot_space where parent_id = ?1))", nativeQuery = true)
	List<Integer> findBusbarAllDevIds(Long spaceId);

	/**
	 * 通过设备名称或者设备编码查找设备
	 * @param nameSn 设备名称或者编号
	 * @return
	 */
	@Query(value = "select id, name, comm_addr from iot_dev where name = ?1 or sn = ?1", nativeQuery = true)
	Map findByNameSn(String nameSn);

	/**
	 * 母排测温温度走势：分钟数据
	 * @param id 设备id
	 * @return
	 */
	@Query(value = "select data_type, the_time time, dvalue from iot_dev_data_win where dev_id = ?1 and the_time between ?2 and ?3 order by the_time", nativeQuery = true)
	List<Map> temperMinuteTrend(Long id, String startTime, String endTime);

	/**
	 * 母排温度走势：小时数据
	 * @param id 设备id
	 * @param startTime 开始时间
	 * @param endTime 结束时间
	 * @return
	 */
	@Query(value = "select data_type, DATE_FORMAT(the_time,'%Y-%m-%d %H') time, dvalue from iot_dev_data_win where dev_id = ?1 and the_time between ?2 and ?3 group by data_type, time order by time", nativeQuery = true)
	List<Map> temperHourTrend(Long id, String startTime, String endTime);

	/**
	 * 根据空间和类型加载
	 * @param iotSpaces
	 * @return
	 */
	List<IotDev> findAllByTypeAndIotSpaceIn (int type,List<IotSpace> iotSpaces);

	/**
	 * 通过空间集合查询所有设备
	 * @param spaceIds
	 * @return
	 */
	@Query(value = "select id, space_id from iot_dev where space_id in (?1)", nativeQuery = true)
	List<Map> findBySpaceIds(List<Long> spaceIds);

	/**
	 * 查询设备所有属性的一小时之内实时数据
	 * @param devId
	 * @return
	 */
	@Query(value = "select * from iot_dev_cur_data_win where dev_id = ?1 and uptime > date_sub(now(), interval 60 MINUTE)", nativeQuery = true)
	List<Map> devItemsCurData(Long devId);

	/**
	 * 查询多个设备一小时之内的实时数据
	 * @param devIds
	 * @return
	 */
	@Query(value = "select * from iot_dev_cur_data_win where dev_id in (?1) and uptime > date_sub(now(), interval 60 MINUTE)", nativeQuery = true)
	List<Map> curDataByDevIds(List<Integer> devIds);

	/**
	 * 查询多个设备的某个属性的数据
	 * @param devIds 设备ids
	 * @param dataType 属性
	 * @return
	 */
	@Query(value = "select dev_id, dvalue from iot_dev_cur_data_win where dev_id in (?1) and data_type = ?2 and uptime > date_sub(now(), interval 60 MINUTE)", nativeQuery = true)
	List<Map> findByDevIdsItem(List<Long> devIds, String dataType);

	/**
	 * 查询设备某个属性一小时之内的实时数据
	 * @param devId
	 * @param dataType
	 * @return
	 */
	@Query(value = "select dvalue, unit from iot_dev_cur_data_win where dev_id = ?1 and data_type = ?2 and uptime > date_sub(now(), interval 60 MINUTE)", nativeQuery = true)
	Map findByDevIdDataType(Long devId, String dataType);

	/**
	 * 查询设备某个属性当天的数据
	 * @param devId
	 * @param name
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Query(value = "select DATE_FORMAT(the_time,'%H') time, SUM(dvalue) value from iot_dev_data_win where dev_id = ?1 and data_type = ?2 and the_time >= ?3 and the_time <= ?4 group by time", nativeQuery = true)
	List<Map> findDevItemData(Long devId, String name, String startTime, String endTime);

	/**
	 * 通过设备空间id查询设备数据
	 * @param spaceId
	 * @return
	 */
	@Query(value = "select id from iot_dev where space_id = ?1", nativeQuery = true)
	List<Long> findIdsBySpaceId(long spaceId);

	/**
	 * 通过设备空间id查询设备数据
	 * @param spaceId
	 * @return
	 */
	@Query(value = "select id, name from iot_dev where space_id = ?1", nativeQuery = true)
	List<Map> findBySpaceId(Long spaceId);

	/**
	 * 查询控制设备或者机组设备空间下面的第一个默认设备
	 * @param id
	 * @return
	 */
	@Query(value = "select * from iot_dev where space_id = (select id from iot_space where parent_id = ?1 and type = ?2) limit 1", nativeQuery = true)
	IotDev findIotDevBySpaceId(Long id, int type);

	/**
	 * 通过设备类型id查找设备
	 * @param devTypeId
	 * @return
	 */
	@Query(value = "select id from iot_dev where dev_type_id = ?1", nativeQuery = true)
	List<Integer> findDevByDevTypeId(Long devTypeId);

	/**
	 *  清楚设备的数据为 null
	 * @param data
	 * @return
	 */
	@Modifying
	@Query(value = "update iot_dev set space_id = null where id in (?1)",nativeQuery = true)
	int emptySpaceData (List<Long> data);

	/**
	 * 查询 数据日志的数据 以及录入功能
	 * @param devId
	 * @param sTime
	 * @param eTime
	 * @param propName
	 * @param pageable
	 * @return
	 */
	@Query(value = "select * from iot_dev_data_win where dev_id = ?1 and data_type =?4 and the_time > ?2 and the_time < ?3 order by the_time DESC",
			countQuery = "select count(*) from iot_dev_data_win where dev_id = ?1 and data_type =?4 and the_time > ?2 and the_time < ?3 order by the_time DESC",nativeQuery = true)
	Page<Map> loadDataEntry (Long devId, String sTime,String eTime, String propName, Pageable pageable);

    /**
     * 删除数据 iot_dev_data_win
     * @param ids
     * @return
     */
    @Modifying
    @Query(value = "delete from iot_dev_data_win where id in (?1)",nativeQuery = true)
    void delDataLogs (Long[] ids);

    /**
     * 修改数据iot_dev_data_win
     * @param id
     * @param theTime
     * @param dvalue
     * @return
     */
	@Modifying
	@Query(value = "update iot_dev_data_win set dvalue = ?3,the_time = ?2 where id = ?1",nativeQuery = true)
	int updateDataLogs (Long id,String theTime,Double dvalue);


	@Modifying
	@Query(value = "update iot_dev_prop set value = ?2 where id = ?1",nativeQuery = true)
	int updateDeviceParams(Long devId, double saveObj);

	/**
	 * 查询母排测温所有设备的cur表数据
	 * @param spaceId
	 * @param devId
	 * @return
	 */
	@Query(value =  "select dev.id, dev.name, is2.`name` as busName, cur.dvalue, cur.data_type, cur.uptime as the_time, is1.name as workshopName, is1.id as workshopId\n" +
			"from iot_dev_cur_data_win cur\n" +
			"LEFT JOIN iot_space is1 on is1.parent_id = ?1\n" +
			"LEFT JOIN iot_space is2 on is1.id = is2.parent_id\n" +
			"LEFT JOIN iot_dev dev on dev.space_id = is2.id\n" +
			"where if(?2 !=-1,dev.id=?2 ,1=1)\n" +
			"and cur.dev_id = dev.id\n" +
			"and uptime > date_sub(now(), interval 60 MINUTE)\n" +
			"GROUP BY cur.data_type, cur.dev_id",nativeQuery = true)
	List<Map> findAllByIotSpace(long spaceId, long devId);

	/**
	 * 查询母排测温所有设备的win表数据
	 * @param spaceId
	 * @return
	 */
	@Query(value =  "select dev.id, dev.name, is2.`name` as busName, win.dvalue, win.data_type, win.the_time, is1.name as workshopName, is1.id as workshopId\n" +
			"from iot_dev_data_win win \n" +
			"LEFT JOIN iot_space is1 on is1.parent_id = ?1\n" +
			"LEFT JOIN iot_space is2 on is1.id = is2.parent_id\n" +
			"LEFT JOIN iot_dev dev on dev.space_id = is2.id\n" +
			"where if(?2 !=-1,dev.id=?2 ,1=1)\n" +
			"and win.the_time between date_add(now(), interval - 2 hour) and now()\n" +
			"and win.dev_id = dev.id\n" +
			"GROUP BY win.the_time, win.data_type, win.dev_id\n" +
			"ORDER BY win.id desc",nativeQuery = true)
	List<Map> findAllByIotSpaceInWin(long spaceId, long devId);
}
